java Spring Pageable不翻译@Column name

Question

我有实体对象：

@Entity(name = "table")
public class SomeEntity {

    @Id
    @Column(name = "id_column_name")
    public final BigDecimal entityId;

    @Column(name = "table_column_name")
    public final String entityFieldName;

}

我的数据库视图定义如下：

CREATE OR REPLACE FORCE EDITIONABLE VIEW "V_TABLE" ("ID_COLUMN_NAME", "TABLE_COLUMN_NAME", "SOME_OTHER_COLUMN") AS ... (some SQL magic) 

我有一个带有自定义查询的存储库：

@RepositoryRestResource
interface SomeEntityRepository extends PagingAndSortingRepository<SomeEntity, BigDecimal> {

    @Query(value = "select id_column_name, table_column_name FROM V_TABLE where some_other_column = ?#{#parameter} order by ?#{#pageable}",
        countQuery = "SELECT count(*) from V_TABLE v where  some_other_column = ?#{#parameter}",
        nativeQuery = true)
    Page<SomeEntity> findBySomeParameter(@Param("parameter") long parameter, Pageable pageable);
} 

当我使用url请求标准数据时，一切正常： http://localhost:8080/someEntity/search/findBySomeParameter?parameter=25&page=0&size=20

但当我添加排序信息时，它不起作用： http://localhost:8080/someEntity/search/findBySomeParameter?parameter=25&page=0&size=20&sort=entityFieldName,asc 将引发以下异常（我正在使用Oracle数据库）：

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "ENTITYFIELDNAME": invalid identifier

排序字段似乎没有用@Column(name)翻译，而是内联到SQL查询中

有没有办法翻译可分页排序，使其不使用字段名，而是使用列名

Answer 1

这说明了这个问题。请阅读上的第3.1节

显然，本机查询不支持动态排序。实际上，如果您将findBySomeParameter方法更改为采用Sort而不是Pageable，您将得到org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting

使用pageable不会出现异常，分页实际上似乎工作正常，但动态排序并不能替代您发现的列名。在我看来，唯一的解决方案是使用JPQL而不是本机查询，这不是一个问题，只要您需要进行的查询是您提供的查询。为了使用JPQL，您需要将视图映射到SomeEntityView类

编辑 我以为这个问题没有记录在案，但实际上它就在这里official doc

Spring Data JPA does not currently support dynamic sorting for native queries, because it would have to manipulate the actual query declared, which it cannot do reliably for native SQL. You can, however, use native queries for pagination by specifying the count query yourself, as shown in the following example:

Answer 2

在SpringBoot 2.4.3中，这个变通方法对我很有效：

    @PersistenceContext
    private EntityManager entityManager;

// an object ptoperty name to a column name adapter
    private Pageable adaptSortColumnNames(Pageable pageable) {
        if (pageable.getSort().isSorted()) {
            SessionFactory sessionFactory;
            if (entityManager == null || (sessionFactory = entityManager.getEntityManagerFactory().unwrap(SessionFactory.class)) == null)
                return pageable;
            AbstractEntityPersister persister = (AbstractEntityPersister) ((MetamodelImplementor) sessionFactory.getMetamodel()).entityPersister(CommentEntity.class);
            Sort adaptedSort = pageable.getSort().get().limit(1).map(order -> {
                String propertyName = order.getProperty();
                String columnName = persister.getPropertyColumnNames(propertyName)[0];
                return Sort.by(order.getDirection(), columnName);
            }).findFirst().get();
            return PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), adaptedSort);
        }
        return pageable;
    }

    @GetMapping()
    public ResponseEntity<PagedResponse<CommentResponse>> findByTextContainingFts(@RequestParam(value = "text", required = false) String text, Pageable pageable) {
// apply this adapter in controller
        pageable = adaptSortColumnNames(pageable);
        Page<CommentEntity> page = commentRepository.find(text, pageable);
        return ResponseEntity.ok().body(domainMapper.fromPageToPagedResponse(page));
    }